# coding=utf-8
import xlrd

from code_builder.conf import read_conf

def fmt_data(data):
    result = ""
    table = data['SHEET1']
    for one in table:
        id = one['ID']
        name = one['NAME']
        certificate_type_id = one['CERTIFICATE_TYPE_ID']
        levels = one['LEVELS']
        kind = one['KIND']
        sort = one['sort']
        print("insert into CERTIFICATE_LEVEL_DIC values({id},'{name}',{certificate_type_id},{levels},{kind},{sort});".format(id=id,name=name,certificate_type_id=certificate_type_id,levels=levels,kind=kind,sort=sort))
    return result

class read_xls(object):
    def __init__(self):
        self.file_path = "D:\\create_code\\base_excel\\data2.xlsx"

    def get_data(self):
        data = xlrd.open_workbook(self.file_path)
        result = {}
        if len(data.sheets()) != 0:
            #  遍历表格
            for i in range(len(data.sheets())):
                table = data.sheets()[i]
                # 获取表格名称
                table_name = table.name.upper()
                # 获取表格行数
                rows_num = table.nrows
                # 获取表格纵数
                cols_num = table.ncols
                rows = []
                if rows_num != 0 and cols_num != 0:
                    for j in range(rows_num):
                        row = {}
                        tmp_list = table.row_values(j)
                        row['ID'] = int(tmp_list[0])
                        row['NAME'] = tmp_list[1].replace(" ","")
                        row['CERTIFICATE_TYPE_ID'] = int(tmp_list[2])
                        if tmp_list[3] is '':
                            row['LEVELS']=0
                        else:
                            row['LEVELS'] = int(tmp_list[3])
                        if tmp_list[4] is '':
                            row['KIND']=0
                        else:
                            row['KIND'] = int(tmp_list[4])
                        if tmp_list[5] is '':
                            row['sort']=0
                        else:
                            row['sort'] = int(tmp_list[5])
                        rows.append(row)
                    result[table_name] = rows
                else:
                    continue
        return result


if __name__=="__main__":
    a = read_xls()
    print(fmt_data(a.get_data()))